package br.com.zumbits.avd.sqlite;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class FilesSQLite {

    public static final String JDBC = "jdbc:sqlite:/";
    public static final String CLASS_NAME = "org.sqlite.JDBC";
    //
    private Connection connection;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public FilesSQLite(String path) throws ClassNotFoundException, SQLException {
        Class.forName(CLASS_NAME);
        this.connection = DriverManager.getConnection(JDBC + path);
        this.statement = this.connection.createStatement();
    }

    public int dropTable() throws SQLException {
        return this.statement.executeUpdate("drop table if exists files");
    }

    public int createTable() throws SQLException {
        dropTable();
        return this.statement.executeUpdate("create table files (name, version)");
    }

    public void insert(String name, int version) throws SQLException {

        select(name);

        if (this.resultSet.next()) {
            update(name, version);
        } else {
            this.preparedStatement = this.connection.prepareStatement("insert into files values (?, ?);");
            this.preparedStatement.setString(1, name);
            this.preparedStatement.setInt(2, version);
            this.preparedStatement.addBatch();

            this.connection.setAutoCommit(false);
            this.preparedStatement.executeBatch();
            this.connection.setAutoCommit(true);
        }
        this.resultSet.close();
    }

    private boolean update(String name, int version) throws SQLException {
        this.preparedStatement = this.connection.prepareStatement("update files version=" + version + " where name='" + name + "'");
        return this.preparedStatement.execute();
    }

    public boolean delete(String name) throws SQLException {
        this.preparedStatement = this.connection.prepareStatement("delete from files where name='" + name + "'");
        return this.preparedStatement.execute();
    }

    public void select(String name) throws SQLException {
        this.resultSet = this.statement.executeQuery("select version from files where name='" + name + "'");
    }

    public boolean next() throws SQLException {
        return this.resultSet.next();
    }

    public int getInt(String columnLabel) throws SQLException {
        return this.resultSet.getInt(columnLabel);
    }

    public void disconnect() throws SQLException {
        this.connection.close();
    }

    public ResultSet getResultSet() {
        return resultSet;
    }

    /*public static void main(String args[]) {
        try {
            FilesSQLite f = new FilesSQLite("/home/joselito/.avd");
            f.createTable();
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(FilesSQLite.class.getName()).log(Level.SEVERE, null, ex);
        } catch (SQLException ex) {
            Logger.getLogger(FilesSQLite.class.getName()).log(Level.SEVERE, null, ex);
        }
    }*/
}
